/* GSMonthStorageSubDao.java
 *
 * Project/Product: GWBS2009
 *
 * Modify Information:
 * =============================================================================
 *   Author	Date       Description
 *   ----------	---------- ---------------------------------------------------
 *   wzh	2010-5-18	First release
 *
 * Licensed Materials - Property of APEP
 * =============================================================================
 *    (C) COPYRIGHT Shanghai Auto Mart E-Procurement Co., Ltd. 2007 - 2010
 *    All Rights Reserved.
 *
 *    This software is the confidential and proprietary information of
 *    Shanghai Auto Mart E-Procurement Co., Ltd. ("Confidential Information").
 *    You shall not disclose such Confidential Information and shall use it only
 *    in accordance with the terms of the license agreement you entered into 
 *    with Shanghai Auto Mart E-Procurement Co., Ltd.
 *
 * Noties:
 * =============================================================================
 * 
 */

package com.hitrust.gs.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.hitrust.gs.lib.GSLogWriter;
import com.hitrust.gs.lib.db.DBConnectionManager;
import com.hitrust.gs.util.exception.GSException;

public class GSMonthStorageSubDao {
/**
 * 获取所有在期间内的产品库存
 * @param startTime
 * @param endTime
 * @param type
 * @return
 * @throws GSException
 */
    public double getProductStorageCount(String product_code,String warehouse_code) throws GSException {
        String sql = "select sum(product_count) from t_product_storage where product_code='"+product_code+"' and warehouse_code='"+warehouse_code+"'";
        return doSqlByDouble(sql);
    }
    /**
     * 获取供应商名称
     * @param code
     * @return
     * @throws GSException
     */
    public List getProviderName(String code) throws GSException {
        String sql = "select name from t_provider where  code='"+code+"'";
        return doSql(sql);
    }
    /**
     * 获取出入库产品编码
     * @param provider
     * @param startTime
     * @param endTime
     * @param type
     * @param balanceModel
     * @return
     * @throws GSException
     */
    public List getProductCode(String provider,String startTime,String endTime,String balanceModel,String warehouse_code,String position) throws GSException {
        String sql = "select DISTINCT product_code,product_price from t_month_storage_sub where  data_time> '"+startTime+"' and data_time< '"+endTime+"' " +
        		" and status='1' and warehouse_code like '"+warehouse_code+"%' and provider_code like'"+provider+"%'";
        
        if(!balanceModel.equals("")){
            sql = sql +" and balance_mode='" + balanceModel + "'";
        }
        if(!position.equals("")){
           sql=sql+ " and position_code='"+position+"'"; 
        }
        return doSql(sql);
    }
    public List getProductCodeByCategory(String provider,String startTime,String endTime,String balanceModel,String warehouse_code,String position) throws GSException {
        String sql = "select DISTINCT a.product_code,a.product_price from t_month_storage_sub a,t_product b where a.product_code=b.concentricity_code  and a.data_time> '"+startTime+"' and a.data_time< '"+endTime+"' " +
                " and a.status='1' and a.warehouse_code like '"+warehouse_code+"%' and b.category_code like'"+provider+"%'";
        
        if(!balanceModel.equals("")){
            sql = sql +" and a.balance_mode='" + balanceModel + "'";
        }
        if(!position.equals("")){
           sql=sql+ " and a.position_code='"+position+"'"; 
        }
        return doSql(sql);
    }
    public List getProductCodeByWarehouseCategory(String category_code,String startTime,String endTime,String balanceModel,String warehouse_code,String position) throws GSException {
               
        String sql = "select DISTINCT product_code,product_price from t_month_storage_sub where  data_time> '"+startTime+"' and data_time< '"+endTime+"' " +
                " and status='1' and warehouse_code like '"+warehouse_code+"%' and" +
                		" product_code in " +
                		"(select product_code from t_warehouse_category_relation   where warehouse_code like '"+warehouse_code+"%' and category_code in (" +
                				"select category_code from  t_warehouse_category where warehouse_code like '"+warehouse_code+"%' and category_code='"+category_code+"' or parent_category_code ='"+category_code+"')" +
                				")";
        
        if(!balanceModel.equals("")){
            sql = sql +" and balance_mode='" + balanceModel + "'";
        }
        if(!position.equals("")){
           sql=sql+ " and position_code='"+position+"'"; 
        }
        return doSql(sql);
    }
 

/**
 * @param sql
 */
private List doSql(String sql) {
    List list = new ArrayList();
   
    DBConnectionManager tDBConn = DBConnectionManager.getInstance();
    Connection iConn = null;
    Statement st = null;
    ResultSet rs = null;
    try {
        iConn = tDBConn.getConnection();
        st = iConn.createStatement();
        rs = st.executeQuery(sql);
        while(rs.next()){
            List ls = new ArrayList();
            ls.add(rs.getString(1));
            ls.add(new Double(rs.getDouble(2)));
            list.add(ls);
        }
        return list;
    }
    catch (SQLException e) {       
        GSLogWriter.debug(this, e);
        GSLogWriter.error(this, e.getMessage());
        return null;
    }finally{
        try {
            if(rs != null) rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        if(st!=null){
            try {
                st.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        if(iConn!=null){
            try {
                iConn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        
    }
}
private double doSqlByDouble(String sql) {
    DBConnectionManager tDBConn = DBConnectionManager.getInstance();
    Connection iConn = null;
    Statement st = null;
    ResultSet rs = null;
    try {
        iConn = tDBConn.getConnection();
        st = iConn.createStatement();
        rs = st.executeQuery(sql);
        while(rs.next()){
            return rs.getDouble(1);
        }
        return 0;
    }
    catch (SQLException e) {       
        GSLogWriter.debug(this, e);
        GSLogWriter.error(this, e.getMessage());
        return 0;
    }finally{
        try {
            if(rs != null) rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        if(st!=null){
            try {
                st.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        if(iConn!=null){
            try {
                iConn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        
    }
}
}
